package Dao;

import Bean.BeanPlace;
import Bean.BeanUser;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class PlaceDao {
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    public ArrayList<BeanPlace> getPlaces() throws Exception {
        String sql="select place_id,place_name,place_photo,place_introduction from place where place_status=1";
        ArrayList<BeanPlace> places=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
            @Override
            public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanPlace place=new BeanPlace();
                    place.setPlace_id(rs.getInt(1));
                    place.setPlace_name(rs.getString(2));
                    place.setPlace_photo(rs.getString(3));
                    place.setPlace_introduction(rs.getString(4));
                    places.add(place);
                }
                return null;
            }});
        return places;
    }
    public ArrayList<BeanPlace> getPlaceByKeyword(String keyword) throws Exception {
        String sql="select place_id,place_name,place_photo,place_introduction from place where place_status=1 and (place_id like '%"+keyword+"%' or place_name like '%"+keyword+"%')";
        ArrayList<BeanPlace> places=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
            @Override
            public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanPlace place=new BeanPlace();
                    place.setPlace_id(rs.getInt(1));
                    place.setPlace_name(rs.getString(2));
                    place.setPlace_photo(rs.getString(3));
                    place.setPlace_introduction(rs.getString(4));
                    places.add(place);
                }
                return null;
            }});
        return places;
    }
    public void placeupdate(int placeid,String placename,String place_photo,String place_introduction) throws Exception {
        String sql="update place set place_name=?,place_photo=?,place_introduction=? where place_id=?";
        this.jdbcTemplate.update(sql,new Object[] {placename,place_photo,place_introduction,placeid});
    }
    public void placeadd(String placename,String place_photo,String place_introduction) throws Exception {
            String sql="insert into place(place_name,place_photo,place_introduction,place_status) values(?,?,?,1)";
            this.jdbcTemplate.update(sql,new Object[] {placename,place_photo,place_introduction});
    }
    public void placedelete(int placeid) throws Exception{
        String sql="update place set place_status=0 where place_id=?";
        this.jdbcTemplate.update(sql,new Object[] {placeid});
    }
}
